import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class ExcelUploadController {
//准备的测试数据;
    // 假设这是您要读取的本地文件路径
    private static final String LOCAL_FILE_PATH = "D:\\test\\biao.xlsx";

//    public String uploadExcel() {
//        try {
//            // 使用Apache POI读取本地Excel文件
//            readExcelAndInsert(LOCAL_FILE_PATH);
//            return "Success";
//        } catch (IOException | SQLException e) {
//            e.printStackTrace();
//            return "Failed: " + e.getMessage();
//        }
//    }
public static void main(String[] args) throws IOException, SQLException {
    new ExcelUploadController().readExcelAndInsert(LOCAL_FILE_PATH);
}

    private void readExcelAndInsert(String filePath) throws IOException, SQLException {
        // 使用Apache POI读取本地Excel文件（这里以XSSFWorkbook为例，支持.xlsx格式）
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();
            //mapjihe
            // 假设第一行是表头，从第二行开始读取数据
            for (int i = 1; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {  // 确保行不为空
                    String dbName = getStringCellValue(row.getCell(0));
                    String fieldName = getStringCellValue(row.getCell(1));
                    String fieldDescription = getStringCellValue(row.getCell(2));
                    // 将数据插入到字典表
                    insertIntoDictionaryTable(dbName, fieldName, fieldDescription);
                }
            }
        }
    }

    private String getStringCellValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    private void insertIntoDictionaryTable(String dbName, String fieldName, String fieldDescription) throws SQLException {
        // JDBC连接数据库并插入数据
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/heima", "root", "wf896901")) {
            String sql = "INSERT INTO ibcs_test (db_name, table_name, field_name) VALUES (?,?,?)";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                statement.setString(1, dbName);
                statement.setString(2, fieldName);
                statement.setString(3, fieldDescription);
                statement.executeUpdate();
            }
        }
    }
}